Este objeto necessita de um carinho especial porque eles suportam os dados de um processo inteiro como vendas, faturamento, compras, acompanhamento de clientes, etc. Note que podemos juntar tudo num banco de dados se o negócio a ser suportado é pequeno mas se for uma grande empresa você talvez precise até de múltiplos servidores.
Já trabalhei gerenciando 25 servidores MS SQL Server e basicamente 2 pessoas conseguiam fazer o serviço. O restante do pessoal utilizava os servidores mas não mantinham eles.
Abaixo cito as principais queries que são utilizadas em bancos de dados. Tentei colocar só sobre banco de dados mas se eu quiser saber as tabelas do banco de dados temos 2 objetos de interesse unidos.
Os principais comandos sobre bancos de dados são :
| Comando | Efeito |
|---|---|
| use NomeBanco | Seleciona o banco de dados ( NomeBanco ) a ser trabalhado pelas queries. |
| SysObjects | |
| select * from sysobjects where xtype='U' ou exec sp_tables | Lista todas as tabelas de Usuário do Banco de dados atual. |
| select * from sysobjects where xtype='S' | Lista todos os objetos de sistema dentro das tabelas do banco de dados atual. |
| select * from sysobjects where xtype='P' | Lista todas as Stored Procedures do banco de dados atual |
| select * from sysobjects where xtype='X' | Exibe todos os índices de todas as tabelas do banco de dados atual |
| select * from sysobjects where xtype='F' | Exibe todas as Chaves estrangeira ( FK ) do banco de dados atual |
| select * from sysobjects where xtype='PK' | Lista todas as Chaves Primárias ( PK ) do banco de dados atual |
| select * from sysobjects where xtype='UQ' | Lista todas as Chaves Unicas do sistema ( Unique Key Constraint ) |
| select * from sysobjects where xtype='TF' | Lista as Funções cadastradas no sistema. Funções nada mais são que uma sequencia de comandos SQL que retornam qualquer tipo de dados, pode ser um número, texto, tabela, etc. |
| select * from sysobjects where xtype='IF' | Lista as Funções cadastradas no sistema. |
| select * from sysobjects where xtype='V' | Lista as views do sistema. Uma view é uma 'visão' particular de um conjunto de dados. Por exemplo, eu posso numa view agrupar inúmeras tabelas e selecionar colunas específicas de cada uma dessas tabelas e com isto dar uma 'visão' específica da informação requerida. Por exemplo, suponha que você queira exibir as vendas por trimestre de um produto e os dados de vendas estão nas tabelas vendas, clientes, vendasProd,vendasFat. Podemos passar parâmetros para a view e ela executará a pesquisa complexa de uma maneira bem facilitada evitando erros. |
| select * from sysobjects where left(name,3)='TBL' | Lista os Objetos do sistema que tenham as primeiras letras como 'TBL'. No exemplo eu assumi que TBL são prefixos de tabelas mas poderia ser qualquer outra notação dos objetos cadastrados no servidor. |
| select name from sys.triggers where parent_class=1 | Lista as triggers cadastradas no sistema - MS SQL SRV 2005 |
| select name from sys.triggers where type='TA' | Lista as triggers cadastradas no sistema que são disparadas depois('After')- MS SQL SRV 2005 |
| select name from sys.triggers where type='TR' | Lista as triggers cadastradas no sistema que são disparadas antes- MS SQL SRV 2005 |
| select * from sys.sql_dependencies |
Exibe a lista as dependencias de um objeto em relação a outro. Contém informações de dependência entre objetos (exibições, procedimentos e gatilhos) no banco de dados e os objetos (tabelas, exibições e procedimentos) contidos em suas definições. MS SQL SRV 2005. |
| select * from systypes | Lista todos os tipos de dados do servidor. MS SQL 2000 |
| select * from sys.systypes | Lista todos os tipos de dados do servidor. MS SQL 2005 |
| select name from sysobjects where type='TR' | Lista as triggers cadastradas no sistema - MS SQL SRV 2000 |
| select name from sys.triggers where parent_class=1 | Lista as triggers cadastradas no sistema - MS SQL SRV 2005 |
| select name from sys.triggers where type='TA' | Lista as triggers cadastradas no sistema que são disparadas depois('After')- MS SQL SRV 2005 |
| select name from sys.triggers where type='TR' | Lista as triggers cadastradas no sistema que são disparadas antes- MS SQL SRV 2005 |
| select * from sysdepends |
Exibe a lista as dependencias de um objeto em relação a outro. Contém informações de dependência entre objetos (exibições, procedimentos e gatilhos) no banco de dados e os objetos (tabelas, exibições e procedimentos) contidos em suas definições. MS SQL SRV 2000. |
| select * from sys.sql_dependencies |
Exibe a lista as dependencias de um objeto em relação a outro. Contém informações de dependência entre objetos (exibições, procedimentos e gatilhos) no banco de dados e os objetos (tabelas, exibições e procedimentos) contidos em suas definições. MS SQL SRV 2005. |
| select * from systypes | Lista todos os tipos de dados do servidor. MS SQL 2000 |
| select * from sys.systypes | Lista todos os tipos de dados do servidor. MS SQL 2005 |
|
SELECT name, physical_name FROM sys.master_files where database_id = db_id('master') |
Lista os arquivos que compõe o banco de dados selecionado, tipicamente os arquivos .mdf e .ldf incluindo seus nomes e pastas. |
| select * from master..sysmessages | Exibe todas as mensagens de erro emitidas pelo servidor- MS SQL 2000 |
| select * from sys.messages | Exibe todas as mensagens de erro emitidas pelo servidor- MS SQL 2005 |
| select * from master..syslogins where isntgroup=0 and isntname=0 | Lista os logins efetuados até o momento no servidor - MS SQL SRV 2000 |
| select * from sys.sql_logins | Lista os logins efetuados até o momento no servidor - MS SQL SRV 2005 |
| Outras views dos objetos do sistema MS SQL SRV | |
| select * from sysusers | Lista os usuários cadastrados no sistema |
| select * from syssegments | Segmentos - Não funciona no MS SQL SRV 2019 |
| select * from sysconstraints | Exibe as constraints cadastradas no sistema. As constraints são Primary Key (PK), Foreign Key (FK), Unique, Not Null, Check, Default. As constraints determinam condições para que o dado 'seja aceitável' ou se 'torne aceitável' numa coluna de uma tabela. |
| select * from sysfilegroups | Exibe o agrupamento dos arquivos de um banco de dados. Inicialmente só existe o Primário(Primary) |
| SELECT * FROM master.dbo.sysdatabases | Lista os bancos de dados do sitema. Normalmente são master, tempdb, model, msdb. |
| Diversas - Sobre bancos de dados e seus componentes | |
| sp_helpuser | Exibe as informações sobre o usuário cadastradas no servidor SQL |
|
DECLARE @db_id smallint; set @db_id = DB_ID(N'master'); print @db_id |
Retorna o ID do banco de dados ( db_ID ). Muito útil para cruzar as informações com os objetos do sistema |
| RAISERROR('Erro na SP10.', 16, 1) | Determinando a saida de uma Procedure com erro. |
| dbcc checkdb (nome_banco_dados) | Função de teste de bancos |
| dbcc_chectable(nome_tabela) | Função de teste de bancos de tabelas |
| Comando | Efeito |
|---|---|
| use NomeBanco | Seleciona o banco de dados ( NomeBanco ) a ser trabalhado pelas queries. |
| exec xp_msver | No parâmetro 'File Version' temos a versão do MS SQL Server instalado. Em 'Plaform' e 'File Description' temos se ele é 32 ou 64 bits. |
| exec sp_helpdevice | Lista os dispositivos do sistema, se existirem. |
| sp_helpindex sysobjects | Lista os índices do servidor. |
| sp_lock | Lista os Locks ativos no momento no SQL. |
| select 'servidor=' + @@servername + ', Versao=' + @@version | Exibe o nome do servidor na Rede e a versão do servidor ms sql. |
| dbcc showcontig | Exibe a fragmentação das tabelas no bd |
| exec sp_dboption | Opções do Servidor - Não funciona no MS SQL 2019 |
| exec sp_configure | Exibe a configuração do Servidor SQL |
| dbcc memusage | Exibe a utilização de memória - Não funciona no MS SQL 2019. |
| sp_who | Lista quem esta usando / logado no servidor SQL |
| select * from sysobjects where xtype='U' ou exec sp_tables | Lista todas as tabelas de Usuário do Banco de dados atual. |
| select * from sysobjects where xtype='S' | Lista todos os objetos dentro das tabelas de sistemas do MS SQL SRV. |
| select * from sysobjects where xtype='P' | Lista todas as Stored Procedures do banco de dados atual |
| select * from sysobjects where xtype='X' | Exibe todos os índices de todas as tabelas do banco de dados atual |
| select * from sysobjects where xtype='F' | Exibe todas as Chaves estrangeira ( FK ) do banco de dados atual |
| select * from sysobjects where xtype='PK' | Lista todas as Chaves Primárias ( PK ) do banco de dados atual |
| select * from sysobjects where xtype='UQ' | Lista todas as Chaves Unicas do sistema ( Unique Key Constraint ) |
| select * from sysobjects where xtype='TF' | Lista as Funções cadastradas no sistema. Funções nada mais são que uma sequencia de comandos SQL que retornam qualquer tipo de dados, pode ser um número, texto, tabela, etc. |
| select * from sysobjects where xtype='IF' | Lista as Funções cadastradas no sistema. |
| select * from sysobjects where xtype='V' | Lista as views do sistema. Uma view é uma 'visão' particular de um conjunto de dados. Por exemplo, eu posso numa view agrupar inúmeras tabelas e selecionar colunas específicas de cada uma dessas tabelas e com isto dar uma 'visão' específica da informação requerida. Por exemplo, suponha que você queira exibir as vendas por trimestre de um produto e os dados de vendas estão nas tabelas vendas, clientes, vendasProd,vendasFat. Podemos passar parâmetros para a view e ela executará a pesquisa complexa de uma maneira bem facilitada evitando erros. |
| select * from sysusers | Lista os usuários cadastrados no sistema |
| sp_helpuser | Exibe as informações sobre o usuário cadastradas no servidor SQL |
| select * from sysobjects where left(name,3)='TBL' | Lista os Objetos do sistema que tenham as primeiras letras como 'TBL'. No exemplo eu assumi que TBL são prefixos de tabelas mas poderia ser qualquer outra notação dos objetos cadastrados no servidor. |
| select * from syssegments | Segmentos - Não funciona no MS SQL SRV 2019 |
| select * from sysconstraints | Exibe as constraints cadastradas no sistema. As constraints são Primary Key (PK), Foreign Key (FK), Unique, Not Null, Check, Default. As constraints determinam condições para que o dado 'seja aceitável' ou se 'torne aceitável' numa coluna de uma tabela. |
| select * from sysfilegroups | Exibe o agrupamento dos arquivos de um banco de dados. Inicialmente só existe o Primário(Primary) |
| SELECT * FROM master.dbo.sysdatabases | Lista os bancos de dados do sitema. Normalmente são master, tempdb, model, msdb. |
| select * from sysdepends |
Exibe a lista as dependencias de um objeto em relação a outro. Contém informações de dependência entre objetos (exibições, procedimentos e gatilhos) no banco de dados e os objetos (tabelas, exibições e procedimentos) contidos em suas definições. MS SQL SRV 2000. |
|
DECLARE @db_id smallint; set @db_id = DB_ID(N'master'); print @db_id |
Retorna o ID do banco de dados ( db_ID ). Muito útil para cruzar as informações com os objetos do sistema |
| RAISERROR('Erro na SP10.', 16, 1) | Determinando a saida de uma Procedure com erro. |
|
SELECT name, physical_name FROM sys.master_files where database_id = db_id('master') |
Lista os arquivos que compõe o banco de dados selecionado, tipicamente os arquivos .mdf e .ldf incluindo seus nomes e pastas. |
| select * from master..sysmessages | Exibe todas as mensagens de erro emitidas pelo servidor- MS SQL 2000 |
| select * from sys.messages | Exibe todas as mensagens de erro emitidas pelo servidor- MS SQL 2005 |
| select * from master..syslogins where isntgroup=0 and isntname=0 | Lista os logins efetuados até o momento no servidor - MS SQL SRV 2000 |
| select * from sys.sql_logins | Lista os logins efetuados até o momento no servidor - MS SQL SRV 2005 |
| dbcc checkdb (nome_banco_dados) | Função de teste de bancos |
| dbcc_chectable(nome_tabela) | Função de teste de bancos de tabelas |
Notas :
1-Coloquei com fundo azul os recursos que todo administrador de banco de dados tem que manter em sua mente para realizar as tarefas do seu dia-a-dia na atividade.
2-A função DBCC pode tanto verificar como corrigir pequenos erros em tabelas e bancos de dados. Cabe ao administrador se só vai verificar a integridade ou corrigir esses pequenos erros. Esta mudança é feita setando apenas um parâmetro do comando, se deve ou não corrigir. Recomendo ativar a correção mas lembre-se que isto levará mais tempo para ser realizado e deixará a base de dados inativa para ser corrigida.
alter PROCEDURE LE_ARQUIVO_TEXTO (
@FileName varchar(255),
@Text1 varchar(2000) OUT) AS
DECLARE @FS int
DECLARE @OLEResult int
DECLARE @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 begin
PRINT 'ERRO na criação do Scripting.FileSystemObject'
return
end
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1
IF @OLEResult <> 0 begin
PRINT 'Erro no OpenTextFile'
return
end
execute @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Text1 OUT
IF @OLEResult <> 0 begin
PRINT 'Erro no Readline'
return
end
declare @dado_lido varchar(8000)
exec Le_ARQUIVO_TEXTO 'C:\nomearq.TXT',@dado_lido out
print 'Dado lido:' + @dado_lido
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ole Automation Procedures',1
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
A primeira coisa a ser definida é a estrutura fisica dos campos no arquivo texto. Na querie iremos separar esses campos pelo comando substring.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
alter procedure loaddata as
BEGIN
declare @query varchar(7000)
declare @string varchar(1500)
declare @string1 varchar(500)
drop table x
create table x (name varchar(2000))
set @query ='master.dbo.xp_cmdshell "type C:\SAMPLE_FILE.TXT"'
insert x exec (@query)
declare C1 cursor local fast_forward for select name from x;
open c1
fetch next from c1 into @string
while @@FETCH_STATUS = 0
BEGIN
fetch next from c1 into @string1
if (len(@string1)<>0 or @string1<>null)
Begin
set @string=@string+@string1
END
ELSE if @string<>''
Begin
insert into filedata values(
substring(@string,1,16),
substring(@string,17,35),
substring(@string,52,25),
substring(@string,77,25),
substring(@string,102,25),
substring(@string,127,25),
substring(@string,152,25),
substring(@string,177,20),
substring(@string,197,15),
substring(@string,212,15),
substring(@string,227,50),
substring(@string,277,15),
substring(@string,292,17),
substring(@string,309,17),
substring(@string,326,17),
substring(@string,343,17),
substring(@string,360,17),
substring(@string,377,17),
substring(@string,394,3),
substring(@string,397,6),
substring(@string,403,4),
substring(@string,407,6),
substring(@string,413,40),
substring(@string,453,15),
substring(@string,468,1),
substring(@string,469,30),
substring(@string,499,30),
substring(@string,529,30),
substring(@string,559,30),
substring(@string,588,30),
substring(@string,619,30),
substring(@string,649,30),
substring(@string,679,30),
substring(@string,709,30),
substring(@string,739,30)
)
set @string=''
End -- if @string<>''
END -- while @@FETCH_STATUS
END --@@FETCH_STATUS = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE GRAVA_ARQUIVO_TEXTO (
@FileName varchar(255),
@Text1 varchar(2000)) AS
DECLARE @FS int
DECLARE @OLEResult int
DECLARE @FileID int
-- criando o file script object no servidor
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'ERRO na criação do Scripting.FileSystemObject'
--Gravando o texto
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'Erro no WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
exec GRAVA_ARQUIVO_TEXTO 'C:\BKP_LOCAL_SRVX\teste_gravacao.txt','alo, alo...123...isto e um teste'
--pre-requisito : precisa que o oledb esteja habilitado
exec sp_configure 'show advanced options',1
reconfigure
sp_configure
exec sp_configure 'Ole Automation Procedures',1
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
GRAVA_ARQUIVO_TEXTO 'C:\BKP_LOCAL_SRVX\dbcc.bat','TESTE'
1) Serviço MS SQL Server : É o servidor de banco de dados em sí.
2) Serviço MS SQL Agent : Gerencia Jobs, Alertas e Operators
3) Serviço Ms Distributed Transaction Cordinator (DTS) : Permite que clientes heterogêneos acessem as transações do servidor.
1- SQL Server Enterprise Manager Snap-in MS Management Console (MMC) : Cliente administrativo do servidor. Permite administrar o servidor.
2- SQL Server Client Configuration : Configura os componentes de comunicação entre o cliente e o server como protocolos de rede ativos ou não.
3- SQL Server Performance Monitor : Permite monitorar em real-time as estatísticas de performance do servidor permitindo uma análise administrativa de seu desempenho.
4- SQL Server Profiler : Permite salvar continuamente as atividades do servidor bem como auditoria.
5- SQL Server Query Analyser : Ferramenta gráfica que permite interagir com a base de dados. Permite visualizar o plano de execução da query, as informações de estatísticas e performance do servidor.
6 - SQL Server Service Management : Permite verificar e alterar o estado dos serviços do ms sql ( Iniciar, Parar, pausar)
7 - SQL Server Setup : Permite instalar ou reconfigurar o servidor sql
8- SQL Server Wizards : É uma coleção de ferramentas que guiam os usuários na execução de tarefas complexas.
1 - Gerenciamento de alertas - alert management : Resultado de processos quando um job é completado ( via event log do nt )
2 - Notificações - notifications : Emissão de Email ligado aos alertas
3- Execução de Jobs - job execution : Executa os jobs agendados
4 - Gerenciamento de replicação - Replication Management :Sincroniza os dados entre os servidores.
Versão MS SQL - Versão Framework
2003 - 1.0
2005 - 2.0
2008 - 3.5
2010 - 4.0
exec xp_cmdshell "dir"
exec xp_loginconfig
sp_help sysdatabases
exec xp_msver
SELECT sysobjects.name AS table_name, syscolumns.name AS column_name,
systypes.name AS datatype, syscolumns.length AS length
FROM sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE (sysobjects.xtype = 'U')
ORDER BY sysobjects.name, syscolumns.colid
SELECT sysobjects.name AS table_name, syscolumns.name AS column_name,
systypes.name AS datatype, syscolumns.LENGTH AS LENGTH
FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE (sysobjects.xtype = 'U')ORDER BY sysobjects.name,
syscolumns.colid
Muitas tarefas de manutenção são feitas em horários de baixa ou nenhuma utilização do servidor. O objetivo é aproveitar esse tempo ocioso para garantir o perfeito funcionamento do servidor amanhã e sempre.
O mais importante é listar as atividades de manutenção a serem executadas e definir o horário que serão executadas levando em conta o tempo que elas demoram para ser executadas. Jamais devemos executar duas tarefas de manutenção ao mesmo tempo especialmente se elas forem competitivas com o mesmo tipo de recurso do servidor.
Abaixo cito um modelo de tarefas e do escalonamento para execução das mesmas
20 hs -> Check da integridade dos bancos de dados ( DBCC CHECKDB )
21 hs -> Compactação dos bancos de dados ( DBCC SHRINKDB )
23:30 hs -> Backup Full das bases agendadas
07:40 hs -> SPs de Atualização dados diários